package com.data.reports.export.excel;

import com.data.reports.export.excel.annotation.ExcelSheet;
import com.data.reports.export.excel.util.FieldReflectionUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * Excel导入工具
 *
 * @author hujifang 2017-09-08 22:41:19
 */
public class ExcelImportUtil {
    /**
     * 从Workbook导入Excel文件，并封装成对象
     *
     * @param sheetClass
     * @param workbook
     * @return
     */
    public static <T> List<T> importExcel(Class<T> sheetClass, Workbook workbook) {
        return importExcel(sheetClass,workbook,0,0);
    }


    /**
     * 从Workbook导入Excel文件，并封装成对象
     *
     * @param sheetClass
     * @param workbook
     * @param headerLine 标题行下标
     * @param tailLineNum 末尾非数据行的行数
     * @return
     */
    public static <T> List<T> importExcel(Class<T> sheetClass, Workbook workbook, int headerLine, int tailLineNum) {
        try {
            // sheet
            ExcelSheet excelSheet = sheetClass.getAnnotation(ExcelSheet.class);
            String sheetName = (excelSheet!=null && excelSheet.name()!=null && excelSheet.name().trim().length()>0)?excelSheet.name().trim():sheetClass.getSimpleName();

            // sheet field
            List<Field> fields = new ArrayList<>();
            if (sheetClass.getDeclaredFields()!=null && sheetClass.getDeclaredFields().length>0) {
                for (Field field: sheetClass.getDeclaredFields()) {
                    if (Modifier.isStatic(field.getModifiers())) {
                        continue;
                    }
                    fields.add(field);
                }
            }

            if (fields.size() == 0) {
                throw new IllegalArgumentException("data fields can not be null.");
            }

            // sheet data
            Sheet sheet = workbook.getSheet(sheetName)!=null?workbook.getSheet(sheetName):workbook.getSheetAt(0);

            Iterator<Row> sheetIterator = sheet.rowIterator();
            int rowIndex = 0;
            List<T> dataList = new ArrayList<>();
            while (sheetIterator.hasNext()) {
                if (rowIndex+tailLineNum>sheet.getPhysicalNumberOfRows()){
                    break;
                }
                Row rowX = sheetIterator.next();
                if (isRowEmpty(rowX)){
                    continue;
                }
                if (rowIndex > headerLine) {
                    T rowObj = sheetClass.newInstance();
                    for (int i = 0; i < fields.size(); i++) {
                        Field field = fields.get(i);
                        String fieldValueStr = getCellValueByCell(workbook, rowX.getCell(i));

                        Object fieldValue = FieldReflectionUtil.parseValue(field, fieldValueStr);

                        field.setAccessible(true);
                        field.set(rowObj, fieldValue);
                    }
                    dataList.add(rowObj);
                }
                rowIndex++;
            }
            return dataList;
        } catch (IllegalAccessException | InstantiationException e) {
            throw new IllegalArgumentException(e);
        }
    }

    //获取单元格各类型值，返回字符串类型
    private static String getCellValueByCell(Workbook workbook, Cell cell) {
        //判断是否为null或空串
        if (cell == null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue;
        CellType cellType = cell.getCellTypeEnum();
        if(cellType==CellType.FORMULA){ //表达式类型
            FormulaEvaluator evaluator=workbook.getCreationHelper().createFormulaEvaluator();
            cellType=evaluator.evaluate(cell).getCellTypeEnum();
        }

        if (cellType == CellType.STRING) { //字符串类型
            cellValue = cell.getStringCellValue().trim();
            cellValue = StringUtils.isBlank(cellValue) ? "" : cellValue;
        }else if (cellType == CellType.BOOLEAN) { //布尔类型
            cellValue = String.valueOf(cell.getBooleanCellValue());
        } else if (cellType == CellType.NUMERIC){ //数值类型
            if (HSSFDateUtil.isCellDateFormatted(cell)) {  //判断日期类型
                cellValue = new DecimalFormat("yyyy-MM-dd").format(cell.getDateCellValue());
            } else { //否
                cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());
            }
        }else {
            cellValue = "";
        }
        return cellValue;
    }

    public static boolean isRowEmpty(Row row) {
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellTypeEnum() != CellType.BLANK)
                return false;
        }
        return true;
    }

    /**
     * 导入Excel文件，并封装成对象
     *
     * @param sheetClass
     * @param excelFile
     * @return
     */
    public static <T> List<T> importExcel(Class<T> sheetClass, File excelFile) {
        try {
            Workbook workbook = WorkbookFactory.create(excelFile);
            return importExcel(sheetClass, workbook);
        } catch (IOException | InvalidFormatException e) {
            throw new IllegalArgumentException(e);
        }
    }

    /**
     * 导入Excel文件，并封装成对象
     *
     * @param sheetClass
     * @param excelFile
     * @param headerLine 标题行下标
     * @param tailLineNum 末尾非数据行的行数
     * @return
     */
    public static <T> List<T> importExcel(Class<T> sheetClass, File excelFile,int headerLine,int tailLineNum) {
        try {
            Workbook workbook = WorkbookFactory.create(excelFile);
            return importExcel(sheetClass, workbook,headerLine,tailLineNum);
        } catch (IOException | InvalidFormatException e) {
            throw new IllegalArgumentException(e);
        }
    }

    /**
     * 从文件路径导入Excel文件，并封装成对象
     *
     * @param sheetClass
     * @param filePath
     * @return
     */
    public static <T> List<T> importExcel(Class<T> sheetClass, String filePath) {
        File excelFile = new File(filePath);
        return importExcel(sheetClass, excelFile);
    }

    /**
     * 从文件路径导入Excel文件，并封装成对象
     *
     * @param sheetClass
     * @param filePath
     * @param headerLine 标题行下标
     * @param tailLineNum 末尾非数据行的行数
     * @return
     */
    public static <T> List<T> importExcel(Class<T> sheetClass, String filePath,int headerLine,int tailLineNum) {
        File excelFile = new File(filePath);
        return importExcel(sheetClass, excelFile,headerLine,tailLineNum);
    }

    /**
     * 导入Excel数据流，并封装成对象
     *
     * @param sheetClass
     * @param inputStream
     * @return
     */
    public static <T> List<T> importExcel(Class<T> sheetClass, InputStream inputStream) {
        try {
            Workbook workbook = WorkbookFactory.create(inputStream);
            return importExcel(sheetClass, workbook);
        } catch (IOException | InvalidFormatException e) {
            throw new IllegalArgumentException(e);
        }
    }

    /**
     * 导入Excel数据流，并封装成对象
     *
     * @param sheetClass
     * @param inputStream
     * @param headerLine 标题行下标
     * @param tailLineNum 末尾非数据行的行数
     * @return
     */
    public static <T> List<T> importExcel(Class<T> sheetClass, InputStream inputStream,int headerLine,int tailLineNum) {
        try {
            Workbook workbook = WorkbookFactory.create(inputStream);
            return importExcel(sheetClass, workbook,headerLine,tailLineNum);
        } catch (IOException | InvalidFormatException e) {
            throw new IllegalArgumentException(e);
        }
    }

}
